\documentclass[11pt,hyperref,]{ctexart}
\usepackage{lmodern}
\usepackage{amssymb,amsmath}
\usepackage{ifxetex,ifluatex}
\usepackage{fixltx2e} % provides \textsubscript
\ifnum 0\ifxetex 1\fi\ifluatex 1\fi=0 % if pdftex
  \usepackage[T1]{fontenc}
  \usepackage[utf8]{inputenc}
\else % if luatex or xelatex
  \ifxetex
    \usepackage{xltxtra,xunicode}
  \else
    \usepackage{fontspec}
  \fi
  \defaultfontfeatures{Mapping=tex-text,Scale=MatchLowercase}
  \newcommand{\euro}{€}
\fi
% use upquote if available, for straight quotes in verbatim environments
\IfFileExists{upquote.sty}{\usepackage{upquote}}{}
% use microtype if available
\IfFileExists{microtype.sty}{%
\usepackage{microtype}
\UseMicrotypeSet[protrusion]{basicmath} % disable protrusion for tt fonts
}{}
\usepackage[left = 1cm, right = 1cm, top = 2.0cm, bottom = 2.0cm]{geometry}
\ifxetex
  \usepackage[setpagesize=false, % page size defined by xetex
              unicode=false, % unicode breaks when used with xetex
              xetex]{hyperref}
\else
  \usepackage[unicode=true]{hyperref}
\fi
\usepackage[usenames,dvipsnames]{color}
\hypersetup{breaklinks=true,
            bookmarks=true,
            pdfauthor={Heaven},
            pdftitle={财务数据分析},
            colorlinks=true,
            citecolor=blue,
            urlcolor=blue,
            linkcolor=magenta,
            pdfborder={0 0 0}}
\urlstyle{same}  % don't use monospace font for urls
\usepackage{color}
\usepackage{fancyvrb}
\newcommand{\VerbBar}{|}
\newcommand{\VERB}{\Verb[commandchars=\\\{\}]}
\DefineVerbatimEnvironment{Highlighting}{Verbatim}{commandchars=\\\{\}}
% Add ',fontsize=\small' for more characters per line
\usepackage{framed}
\definecolor{shadecolor}{RGB}{248,248,248}
\newenvironment{Shaded}{\begin{snugshade}}{\end{snugshade}}
\newcommand{\KeywordTok}[1]{\textcolor[rgb]{0.13,0.29,0.53}{\textbf{#1}}}
\newcommand{\DataTypeTok}[1]{\textcolor[rgb]{0.13,0.29,0.53}{#1}}
\newcommand{\DecValTok}[1]{\textcolor[rgb]{0.00,0.00,0.81}{#1}}
\newcommand{\BaseNTok}[1]{\textcolor[rgb]{0.00,0.00,0.81}{#1}}
\newcommand{\FloatTok}[1]{\textcolor[rgb]{0.00,0.00,0.81}{#1}}
\newcommand{\ConstantTok}[1]{\textcolor[rgb]{0.00,0.00,0.00}{#1}}
\newcommand{\CharTok}[1]{\textcolor[rgb]{0.31,0.60,0.02}{#1}}
\newcommand{\SpecialCharTok}[1]{\textcolor[rgb]{0.00,0.00,0.00}{#1}}
\newcommand{\StringTok}[1]{\textcolor[rgb]{0.31,0.60,0.02}{#1}}
\newcommand{\VerbatimStringTok}[1]{\textcolor[rgb]{0.31,0.60,0.02}{#1}}
\newcommand{\SpecialStringTok}[1]{\textcolor[rgb]{0.31,0.60,0.02}{#1}}
\newcommand{\ImportTok}[1]{#1}
\newcommand{\CommentTok}[1]{\textcolor[rgb]{0.56,0.35,0.01}{\textit{#1}}}
\newcommand{\DocumentationTok}[1]{\textcolor[rgb]{0.56,0.35,0.01}{\textbf{\textit{#1}}}}
\newcommand{\AnnotationTok}[1]{\textcolor[rgb]{0.56,0.35,0.01}{\textbf{\textit{#1}}}}
\newcommand{\CommentVarTok}[1]{\textcolor[rgb]{0.56,0.35,0.01}{\textbf{\textit{#1}}}}
\newcommand{\OtherTok}[1]{\textcolor[rgb]{0.56,0.35,0.01}{#1}}
\newcommand{\FunctionTok}[1]{\textcolor[rgb]{0.00,0.00,0.00}{#1}}
\newcommand{\VariableTok}[1]{\textcolor[rgb]{0.00,0.00,0.00}{#1}}
\newcommand{\ControlFlowTok}[1]{\textcolor[rgb]{0.13,0.29,0.53}{\textbf{#1}}}
\newcommand{\OperatorTok}[1]{\textcolor[rgb]{0.81,0.36,0.00}{\textbf{#1}}}
\newcommand{\BuiltInTok}[1]{#1}
\newcommand{\ExtensionTok}[1]{#1}
\newcommand{\PreprocessorTok}[1]{\textcolor[rgb]{0.56,0.35,0.01}{\textit{#1}}}
\newcommand{\AttributeTok}[1]{\textcolor[rgb]{0.77,0.63,0.00}{#1}}
\newcommand{\RegionMarkerTok}[1]{#1}
\newcommand{\InformationTok}[1]{\textcolor[rgb]{0.56,0.35,0.01}{\textbf{\textit{#1}}}}
\newcommand{\WarningTok}[1]{\textcolor[rgb]{0.56,0.35,0.01}{\textbf{\textit{#1}}}}
\newcommand{\AlertTok}[1]{\textcolor[rgb]{0.94,0.16,0.16}{#1}}
\newcommand{\ErrorTok}[1]{\textcolor[rgb]{0.64,0.00,0.00}{\textbf{#1}}}
\newcommand{\NormalTok}[1]{#1}
\RecustomVerbatimEnvironment{Highlighting}{Verbatim}{commandchars=\\\{\},formatcom=\xeCJKVerbAddon}
\usepackage{longtable,booktabs}
\usepackage{graphicx,grffile}
\makeatletter
\def\maxwidth{\ifdim\Gin@nat@width>\linewidth\linewidth\else\Gin@nat@width\fi}
\def\maxheight{\ifdim\Gin@nat@height>\textheight\textheight\else\Gin@nat@height\fi}
\makeatother
% Scale images if necessary, so that they will not overflow the page
% margins by default, and it is still possible to overwrite the defaults
% using explicit options in \includegraphics[width, height, ...]{}
\setkeys{Gin}{width=\maxwidth,height=\maxheight,keepaspectratio}
\setlength{\emergencystretch}{3em}  % prevent overfull lines
\providecommand{\tightlist}{%
  \setlength{\itemsep}{0pt}\setlength{\parskip}{0pt}}
\setcounter{secnumdepth}{5}

\title{财务数据分析}
\author{Heaven}
\date{2018年02月09日}

% Redefines (sub)paragraphs to behave more like sections
\ifx\paragraph\undefined\else
\let\oldparagraph\paragraph
\renewcommand{\paragraph}[1]{\oldparagraph{#1}\mbox{}}
\fi
\ifx\subparagraph\undefined\else
\let\oldsubparagraph\subparagraph
\renewcommand{\subparagraph}[1]{\oldsubparagraph{#1}\mbox{}}
\fi

\begin{document}
\maketitle

{
\setcounter{tocdepth}{2}
\tableofcontents
}
\subsection{简单数据分析}

\subsubsection{费用结构分析}

\small

\begin{Shaded}
\begin{Highlighting}[]
\NormalTok{df1.}\DecValTok{1}\NormalTok{ <-}\StringTok{ }\NormalTok{readxl}\OperatorTok{::}\KeywordTok{read_excel}\NormalTok{(df.fpath, }\DataTypeTok{sheet =} \StringTok{"c1.1"}\NormalTok{) }

\NormalTok{hg_pie <-}\StringTok{ }\ControlFlowTok{function}\NormalTok{(df_, group_, value_, }\DataTypeTok{show.legend =} \OtherTok{FALSE}\NormalTok{ ) \{}
  \KeywordTok{require}\NormalTok{(ggpubr, }\DataTypeTok{quietly =} \OtherTok{TRUE}\NormalTok{)}
  \KeywordTok{require}\NormalTok{(ggrepel, }\DataTypeTok{quietly =} \OtherTok{TRUE}\NormalTok{)}
\NormalTok{  hg_theme_pie <-}\StringTok{ }\KeywordTok{theme_minimal}\NormalTok{() }\OperatorTok{+}\StringTok{ }\KeywordTok{theme}\NormalTok{(}
  \DataTypeTok{axis.title.x =} \KeywordTok{element_blank}\NormalTok{(),}
  \DataTypeTok{axis.title.y =} \KeywordTok{element_blank}\NormalTok{(),}
  \DataTypeTok{axis.line.y =} \KeywordTok{element_blank}\NormalTok{(),}
  \DataTypeTok{axis.text.x=}\KeywordTok{element_blank}\NormalTok{(),}
  \DataTypeTok{axis.text.y =} \KeywordTok{element_blank}\NormalTok{(),}
  \DataTypeTok{panel.border =} \KeywordTok{element_blank}\NormalTok{(),}
  \DataTypeTok{panel.grid =} \KeywordTok{element_blank}\NormalTok{(),}
  \DataTypeTok{axis.ticks =} \KeywordTok{element_blank}\NormalTok{(),}
  \DataTypeTok{plot.title =} \KeywordTok{element_text}\NormalTok{(}\DataTypeTok{size=}\DecValTok{14}\NormalTok{, }\DataTypeTok{face=}\StringTok{"bold"}\NormalTok{)}
\NormalTok{  ) }
  
\NormalTok{  df_ <-}\StringTok{ }\KeywordTok{as.data.frame}\NormalTok{(df_)}
\NormalTok{  df_[,group_] <-}\StringTok{ }\KeywordTok{reorder}\NormalTok{(df_[,group_], df_[,value_])}
\NormalTok{  df_ <-}\StringTok{ }\NormalTok{df_[}\KeywordTok{order}\NormalTok{(df_[, value_], }\DataTypeTok{decreasing =} \OtherTok{TRUE}\NormalTok{), ]}
\NormalTok{  df_}\OperatorTok{$}\NormalTok{pct <-}\StringTok{  }\KeywordTok{round}\NormalTok{(df_[, value_]}\OperatorTok{/}\KeywordTok{sum}\NormalTok{(df_[, value_]), }\DecValTok{2}\NormalTok{)}
\NormalTok{  df_}\OperatorTok{$}\NormalTok{label <-}\StringTok{ }\KeywordTok{paste0}\NormalTok{(df_[,group_], }\StringTok{" "}\NormalTok{,}\KeywordTok{format}\NormalTok{(df_}\OperatorTok{$}\NormalTok{pct, }\DataTypeTok{nsmall =} \DecValTok{2}\NormalTok{), }\StringTok{"%"}\NormalTok{)}
\NormalTok{  df_}\OperatorTok{$}\NormalTok{label_y <-}\StringTok{ }\KeywordTok{cumsum}\NormalTok{(df_}\OperatorTok{$}\NormalTok{pct) }\OperatorTok{-}\StringTok{ }\NormalTok{df_}\OperatorTok{$}\NormalTok{pct}\OperatorTok{/}\DecValTok{2}
  
  \KeywordTok{ggplot}\NormalTok{(df_, }\KeywordTok{aes}\NormalTok{(}\DataTypeTok{x=}\StringTok{""}\NormalTok{, }\DataTypeTok{y=}\NormalTok{pct, }\DataTypeTok{fill=}\KeywordTok{get}\NormalTok{(group_))) }\OperatorTok{+}
\StringTok{    }\KeywordTok{geom_bar}\NormalTok{(}\DataTypeTok{width =} \DecValTok{1}\NormalTok{, }\DataTypeTok{stat =} \StringTok{"identity"}\NormalTok{) }\OperatorTok{+}
\StringTok{    }\NormalTok{ggrepel}\OperatorTok{::}\KeywordTok{geom_label_repel}\NormalTok{(}\KeywordTok{aes}\NormalTok{(}\DataTypeTok{label =}\NormalTok{ label, }\DataTypeTok{y =}\NormalTok{ label_y, }\DataTypeTok{x =} \FloatTok{1.3}\NormalTok{), }\DataTypeTok{min.segment.length =} \FloatTok{0.1}\NormalTok{) }\OperatorTok{+}
\StringTok{    }\KeywordTok{coord_polar}\NormalTok{(}\StringTok{"y"}\NormalTok{, }\DataTypeTok{start=}\DecValTok{0}\NormalTok{) }\OperatorTok{+}
\StringTok{    }\NormalTok{hg_theme_pie }\OperatorTok{+}
\StringTok{    }\KeywordTok{guides}\NormalTok{(}\DataTypeTok{fill =} \StringTok{"none"}\NormalTok{) }
\NormalTok{\}}
\NormalTok{df1.}\FloatTok{1.}\NormalTok{pieplot <-}\StringTok{ }\KeywordTok{hg_pie}\NormalTok{(}\DataTypeTok{df_ =}\NormalTok{ df1.}\DecValTok{1}\NormalTok{, }\DataTypeTok{group_ =} \StringTok{"项目"}\NormalTok{, }\DataTypeTok{value_ =} \StringTok{"金额"}\NormalTok{) }\OperatorTok{+}
\StringTok{  }\KeywordTok{ggtitle}\NormalTok{(}\StringTok{"费用结构分析一"}\NormalTok{)}

\NormalTok{df1.}\FloatTok{1.}\NormalTok{plot <-}\StringTok{ }\NormalTok{df1.}\DecValTok{1} \OperatorTok{%>%}\StringTok{ }\KeywordTok{arrange}\NormalTok{(}\OperatorTok{-}\NormalTok{金额) }\OperatorTok{%>%}\StringTok{ }
\StringTok{  }\KeywordTok{ggtexttable}\NormalTok{(}\DataTypeTok{rows =} \OtherTok{NULL}\NormalTok{, }\DataTypeTok{theme =} \KeywordTok{ttheme}\NormalTok{(}\StringTok{"mOrange"}\NormalTok{))}

\KeywordTok{ggarrange}\NormalTok{(df1.}\FloatTok{1.}\NormalTok{plot, df1.}\FloatTok{1.}\NormalTok{pieplot)}
\end{Highlighting}
\end{Shaded}

\includegraphics{tp391.13-629-1-excel-home-finacial_files/figure-latex/unnamed-chunk-1-1.pdf}

\normalsize

\subsubsection{费用结构分析二}

\small

\begin{Shaded}
\begin{Highlighting}[]
\NormalTok{df1.}\DecValTok{2}\NormalTok{ <-}\StringTok{ }\NormalTok{readxl}\OperatorTok{::}\KeywordTok{read_excel}\NormalTok{(df.fpath, }\DataTypeTok{sheet =} \StringTok{"c1.2"}\NormalTok{) }

\NormalTok{df1.}\FloatTok{2.1}\NormalTok{.plot <-}\StringTok{ }\NormalTok{df1.}\DecValTok{2} \OperatorTok{%>%}\StringTok{ }\KeywordTok{arrange}\NormalTok{(}\OperatorTok{-}\NormalTok{金额) }\OperatorTok{%>%}\StringTok{ }
\StringTok{  }\KeywordTok{slice}\NormalTok{(}\DecValTok{1}\OperatorTok{:}\NormalTok{(}\KeywordTok{nrow}\NormalTok{(.)}\OperatorTok{/}\DecValTok{2}\OperatorTok{+}\DecValTok{1}\NormalTok{)) }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{ggtexttable}\NormalTok{(}\DataTypeTok{rows =} \OtherTok{NULL}\NormalTok{, }\DataTypeTok{theme =} \KeywordTok{ttheme}\NormalTok{(}\StringTok{"mOrange"}\NormalTok{, }\DataTypeTok{base_size =} \DecValTok{8}\NormalTok{))}
\NormalTok{df1.}\FloatTok{2.2}\NormalTok{.plot <-}\StringTok{ }\NormalTok{df1.}\DecValTok{2} \OperatorTok{%>%}\StringTok{ }\KeywordTok{arrange}\NormalTok{(}\OperatorTok{-}\NormalTok{金额) }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{slice}\NormalTok{((}\KeywordTok{nrow}\NormalTok{(.)}\OperatorTok{/}\DecValTok{2}\OperatorTok{+}\DecValTok{1}\NormalTok{)}\OperatorTok{:}\KeywordTok{nrow}\NormalTok{(.)) }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{ggtexttable}\NormalTok{(}\DataTypeTok{rows =} \OtherTok{NULL}\NormalTok{, }\DataTypeTok{theme =} \KeywordTok{ttheme}\NormalTok{(}\StringTok{"mOrange"}\NormalTok{, }\DataTypeTok{base_size =} \DecValTok{8}\NormalTok{))}


\NormalTok{df1.}\FloatTok{2.}\NormalTok{pieplot <-}\StringTok{ }\KeywordTok{hg_pie}\NormalTok{(df1.}\DecValTok{2}\NormalTok{, }\StringTok{"项目"}\NormalTok{, }\StringTok{"金额"}\NormalTok{) }\OperatorTok{+}
\StringTok{  }\KeywordTok{ggtitle}\NormalTok{(}\StringTok{"费用结构分析二"}\NormalTok{)}

\KeywordTok{ggarrange}\NormalTok{(df1.}\FloatTok{2.1}\NormalTok{.plot, df1.}\FloatTok{2.2}\NormalTok{.plot, df1.}\FloatTok{2.}\NormalTok{pieplot,}
          \DataTypeTok{ncol =} \DecValTok{3}\NormalTok{, }
          \DataTypeTok{widths =} \KeywordTok{c}\NormalTok{(}\DecValTok{1}\OperatorTok{/}\DecValTok{5}\NormalTok{, }\DecValTok{1}\OperatorTok{/}\DecValTok{5}\NormalTok{, }\DecValTok{3}\OperatorTok{/}\DecValTok{5}\NormalTok{))}
\end{Highlighting}
\end{Shaded}

\includegraphics{tp391.13-629-1-excel-home-finacial_files/figure-latex/unnamed-chunk-2-1.pdf}

\begin{Shaded}
\begin{Highlighting}[]
\KeywordTok{ggplot}\NormalTok{(df1.}\DecValTok{2}\NormalTok{, }\KeywordTok{aes}\NormalTok{(}\KeywordTok{reorder}\NormalTok{(项目, 金额), 金额)) }\OperatorTok{+}\StringTok{ }
\StringTok{  }\KeywordTok{geom_bar}\NormalTok{(}\DataTypeTok{stat =} \StringTok{"identity"}\NormalTok{) }\OperatorTok{+}
\StringTok{  }\KeywordTok{geom_text}\NormalTok{(}\KeywordTok{aes}\NormalTok{(}\DataTypeTok{label =}\NormalTok{ 金额), }\DataTypeTok{hjust =} \OperatorTok{-}\FloatTok{0.2}\NormalTok{) }\OperatorTok{+}
\StringTok{  }\KeywordTok{scale_y_continuous}\NormalTok{(}\DataTypeTok{breaks =} \KeywordTok{seq}\NormalTok{(}\DecValTok{0}\NormalTok{, }\KeywordTok{max}\NormalTok{(df1.}\DecValTok{2}\OperatorTok{$}\NormalTok{金额) , }\DecValTok{5000}\NormalTok{),}
                     \DataTypeTok{limits =} \KeywordTok{c}\NormalTok{(}\DecValTok{0}\NormalTok{, }\KeywordTok{max}\NormalTok{(df1.}\DecValTok{2}\OperatorTok{$}\NormalTok{金额) }\OperatorTok{*}\StringTok{ }\FloatTok{1.1}\NormalTok{)) }\OperatorTok{+}
\StringTok{  }\KeywordTok{coord_flip}\NormalTok{() }\OperatorTok{+}\StringTok{ }
\StringTok{  }\KeywordTok{xlab}\NormalTok{(}\StringTok{""}\NormalTok{) }\OperatorTok{+}
\StringTok{  }\KeywordTok{ggtitle}\NormalTok{(}\StringTok{"费用结构分析二"}\NormalTok{)}
\end{Highlighting}
\end{Shaded}

\includegraphics{tp391.13-629-1-excel-home-finacial_files/figure-latex/unnamed-chunk-2-2.pdf}

\normalsize

\subsubsection{横向费用对比分析表}

\small

\begin{Shaded}
\begin{Highlighting}[]
\NormalTok{df1.}\DecValTok{3}\NormalTok{ <-}\StringTok{ }\NormalTok{readxl}\OperatorTok{::}\KeywordTok{read_excel}\NormalTok{(df.fpath, }\DataTypeTok{sheet =} \StringTok{"c1.3"}\NormalTok{) }

\CommentTok{# 堆积图}
\NormalTok{df1.}\DecValTok{3} \OperatorTok{%>%}\StringTok{ }\KeywordTok{gather}\NormalTok{(}\DataTypeTok{key =} \StringTok{"公司"}\NormalTok{, }\DataTypeTok{value =} \StringTok{"金额"}\NormalTok{, }\OperatorTok{-}\NormalTok{项目) }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{group_by}\NormalTok{(项目) }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{arrange}\NormalTok{(项目, }\KeywordTok{desc}\NormalTok{(公司)) }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{mutate}\NormalTok{(}\DataTypeTok{label_y =} \KeywordTok{cumsum}\NormalTok{(金额) }\OperatorTok{-}\StringTok{ }\NormalTok{金额}\OperatorTok{/}\DecValTok{2}\NormalTok{) }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{ungroup}\NormalTok{() }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{ggplot}\NormalTok{(}\KeywordTok{aes}\NormalTok{(}\KeywordTok{reorder}\NormalTok{(项目, }\OperatorTok{-}\NormalTok{金额), 金额, }\DataTypeTok{fill =}\NormalTok{ 公司)) }\OperatorTok{+}
\StringTok{  }\KeywordTok{geom_bar}\NormalTok{(}\DataTypeTok{stat =} \StringTok{"identity"}\NormalTok{) }\OperatorTok{+}
\StringTok{  }\KeywordTok{geom_text}\NormalTok{(}\KeywordTok{aes}\NormalTok{(}\DataTypeTok{label =}\NormalTok{ 金额, }\DataTypeTok{y =}\NormalTok{ label_y)) }\OperatorTok{+}
\StringTok{  }\KeywordTok{ggtitle}\NormalTok{(}\StringTok{"横向费用对比分析图"}\NormalTok{) }\OperatorTok{+}
\StringTok{  }\KeywordTok{xlab}\NormalTok{(}\StringTok{"费用"}\NormalTok{)}
\end{Highlighting}
\end{Shaded}

\includegraphics{tp391.13-629-1-excel-home-finacial_files/figure-latex/unnamed-chunk-3-1.pdf}

\begin{Shaded}
\begin{Highlighting}[]
\NormalTok{df1.}\FloatTok{3.}\NormalTok{plot.table <-}\StringTok{ }\KeywordTok{ggtexttable}\NormalTok{(df1.}\DecValTok{3}\NormalTok{, }\DataTypeTok{rows =} \OtherTok{NULL}\NormalTok{, }\DataTypeTok{theme =} \KeywordTok{ttheme}\NormalTok{(}\DataTypeTok{base_size =} \DecValTok{8}\NormalTok{))}

\NormalTok{df1.}\FloatTok{3.}\NormalTok{plot <-}\StringTok{ }\NormalTok{df1.}\DecValTok{3} \OperatorTok{%>%}\StringTok{ }\KeywordTok{gather}\NormalTok{(}\DataTypeTok{key =} \StringTok{"公司"}\NormalTok{, }\DataTypeTok{value =} \StringTok{"金额"}\NormalTok{, }\OperatorTok{-}\NormalTok{项目) }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{group_by}\NormalTok{(项目) }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{arrange}\NormalTok{(项目, }\KeywordTok{desc}\NormalTok{(公司)) }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{mutate}\NormalTok{(}\DataTypeTok{label_y =} \KeywordTok{cumsum}\NormalTok{(金额) }\OperatorTok{-}\StringTok{ }\NormalTok{金额}\OperatorTok{/}\DecValTok{2}\NormalTok{) }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{ungroup}\NormalTok{() }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{ggplot}\NormalTok{(}\KeywordTok{aes}\NormalTok{(}\KeywordTok{reorder}\NormalTok{(项目, }\OperatorTok{-}\NormalTok{金额), 金额, }\DataTypeTok{fill =}\NormalTok{ 公司)) }\OperatorTok{+}
\StringTok{  }\KeywordTok{geom_bar}\NormalTok{(}\DataTypeTok{stat =} \StringTok{"identity"}\NormalTok{, }\DataTypeTok{position =} \StringTok{"dodge"}\NormalTok{) }\OperatorTok{+}
\StringTok{  }\KeywordTok{geom_text}\NormalTok{(}\KeywordTok{aes}\NormalTok{(}\DataTypeTok{label =}\NormalTok{ 金额), }\DataTypeTok{vjust =} \FloatTok{1.5}\NormalTok{, }\DataTypeTok{position =} \KeywordTok{position_dodge}\NormalTok{(}\FloatTok{0.9}\NormalTok{), }\DataTypeTok{size =} \DecValTok{3}\NormalTok{) }\OperatorTok{+}
\StringTok{  }\KeywordTok{ggtitle}\NormalTok{(}\StringTok{"横向费用对比分析图"}\NormalTok{) }\OperatorTok{+}
\StringTok{  }\KeywordTok{xlab}\NormalTok{(}\StringTok{"费用"}\NormalTok{)}

\KeywordTok{ggarrange}\NormalTok{(df1.}\FloatTok{3.}\NormalTok{plot.table, df1.}\FloatTok{3.}\NormalTok{plot, }\DataTypeTok{ncol =} \DecValTok{1}\NormalTok{, }\DataTypeTok{nrow =} \DecValTok{2}\NormalTok{, }\DataTypeTok{heights =} \KeywordTok{c}\NormalTok{(}\DecValTok{1}\OperatorTok{/}\DecValTok{3}\NormalTok{, }\DecValTok{2}\OperatorTok{/}\DecValTok{3}\NormalTok{) )}
\end{Highlighting}
\end{Shaded}

\includegraphics{tp391.13-629-1-excel-home-finacial_files/figure-latex/unnamed-chunk-3-2.pdf}

\normalsize

\subsubsection{量、价差分析}

\small

\begin{Shaded}
\begin{Highlighting}[]
\NormalTok{df1.}\DecValTok{4}\NormalTok{ <-}\StringTok{ }\NormalTok{readxl}\OperatorTok{::}\KeywordTok{read_excel}\NormalTok{(df.fpath, }\DataTypeTok{sheet =} \StringTok{"c1.4"}\NormalTok{) }

\NormalTok{df1.}\DecValTok{4} \OperatorTok{%>%}\StringTok{ }\KeywordTok{mutate}\NormalTok{(单位差价 =}\StringTok{ }\NormalTok{本期单价 }\OperatorTok{-}\StringTok{ }\NormalTok{上年同期单价) }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{mutate}\NormalTok{(价格影响 =}\StringTok{ }\NormalTok{单位差价 }\OperatorTok{*}\StringTok{ }\NormalTok{同期累计耗量) }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{mutate}\NormalTok{(数量影响 =}\StringTok{ }\NormalTok{(本期累计耗量 }\OperatorTok{-}\StringTok{ }\NormalTok{同期累计耗量) }\OperatorTok{*}\StringTok{ }\NormalTok{本期单价) }\OperatorTok{%>%}
\StringTok{  }\NormalTok{knitr}\OperatorTok{::}\KeywordTok{kable}\NormalTok{()}
\end{Highlighting}
\end{Shaded}

\begin{longtable}[]{@{}llccccccc@{}}
\toprule
材料名称 & 单位 & 本期单价 & 上年同期单价 & 本期累计耗量 & 同期累计耗量
& 单位差价 & 价格影响 & 数量影响\tabularnewline
\midrule
\endhead
钢棒 & KG & 49.00 & 42.00 & 4600 & 2350 & 7.00 & 16450 &
110250\tabularnewline
钢板 & KG & 45.00 & 44.00 & 2500 & 2850 & 1.00 & 2850 &
-15750\tabularnewline
铜棒 & KG & 65.00 & 67.00 & 1290 & 2030 & -2.00 & -4060 &
-48100\tabularnewline
铜板 & KG & 66.00 & 67.00 & 5740 & 6170 & -1.00 & -6170 &
-28380\tabularnewline
煤 & 吨 & 368.00 & 349.00 & 710 & 550 & 19.00 & 10450 &
58880\tabularnewline
水 & 吨 & 3.10 & 2.80 & 19540 & 16890 & 0.30 & 5067 &
8215\tabularnewline
电 & 度 & 0.75 & 0.69 & 90000 & 100000 & 0.06 & 6000 &
-7500\tabularnewline
\bottomrule
\end{longtable}

\normalsize

\subsubsection{产销量分析}

\small

\begin{Shaded}
\begin{Highlighting}[]
\NormalTok{df1.}\DecValTok{5}\NormalTok{ <-}\StringTok{ }\NormalTok{readxl}\OperatorTok{::}\KeywordTok{read_excel}\NormalTok{(df.fpath, }\DataTypeTok{sheet =} \StringTok{"c1.5"}\NormalTok{) }
\NormalTok{df1.}\DecValTok{5}\OperatorTok{$}\NormalTok{报送日期 <-}\StringTok{ }\KeywordTok{as.Date}\NormalTok{(df1.}\DecValTok{5}\OperatorTok{$}\NormalTok{报送日期)}
\NormalTok{df1.}\DecValTok{5} \OperatorTok{%>%}\StringTok{ }\KeywordTok{gather}\NormalTok{(}\DataTypeTok{key =} \StringTok{"分类"}\NormalTok{, }\DataTypeTok{value =} \StringTok{"数量"}\NormalTok{, 本日入库, 本日出库) }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{ggplot}\NormalTok{(}\KeywordTok{aes}\NormalTok{(报送日期, 数量, }\DataTypeTok{fill =} \KeywordTok{factor}\NormalTok{(分类, }\DataTypeTok{levels =} \KeywordTok{c}\NormalTok{(}\StringTok{"本日入库"}\NormalTok{, }\StringTok{"本日出库"}\NormalTok{)))) }\OperatorTok{+}
\StringTok{  }\KeywordTok{geom_bar}\NormalTok{(}\DataTypeTok{stat =} \StringTok{"identity"}\NormalTok{, }\DataTypeTok{position =} \StringTok{"dodge"}\NormalTok{) }\OperatorTok{+}
\StringTok{  }\KeywordTok{guides}\NormalTok{(}\DataTypeTok{fill =} \KeywordTok{guide_legend}\NormalTok{(}\DataTypeTok{title =} \StringTok{"分类"}\NormalTok{)) }\OperatorTok{+}\StringTok{ }
\StringTok{  }\KeywordTok{geom_line}\NormalTok{(}\KeywordTok{aes}\NormalTok{(报送日期, 期末库存)) }\OperatorTok{+}
\StringTok{  }\KeywordTok{scale_x_date}\NormalTok{(}\DataTypeTok{breaks =}\NormalTok{ df1.}\DecValTok{5}\OperatorTok{$}\NormalTok{报送日期) }\OperatorTok{+}
\StringTok{  }\KeywordTok{theme}\NormalTok{(}\DataTypeTok{axis.text.x=}\KeywordTok{element_text}\NormalTok{(}\DataTypeTok{angle=}\DecValTok{45}\NormalTok{)) }\OperatorTok{+}
\StringTok{  }\KeywordTok{ggtitle}\NormalTok{(}\StringTok{"产销量分析"}\NormalTok{)}
\end{Highlighting}
\end{Shaded}

\includegraphics{tp391.13-629-1-excel-home-finacial_files/figure-latex/unnamed-chunk-5-1.pdf}

\normalsize

\subsection{凭证记录查询}

\subsubsection{凭证明细查询表}

\small

\begin{Shaded}
\begin{Highlighting}[]
\NormalTok{kemudaima <-}\StringTok{ }\DecValTok{1131}

\NormalTok{df2.}\FloatTok{1.}\NormalTok{kemu <-}\StringTok{ }\NormalTok{readxl}\OperatorTok{::}\KeywordTok{read_excel}\NormalTok{(df.fpath, }\StringTok{"c2.1.科目表"}\NormalTok{)}
\NormalTok{df2.}\FloatTok{1.}\NormalTok{mingxi <-}\StringTok{ }\NormalTok{readxl}\OperatorTok{::}\KeywordTok{read_excel}\NormalTok{(df.fpath, }\StringTok{"c2.1.凭证明细"}\NormalTok{)}
\NormalTok{df2.}\DecValTok{1}\NormalTok{ <-}\StringTok{ }\NormalTok{df2.}\FloatTok{1.}\NormalTok{mingxi }\OperatorTok{%>%}\StringTok{ }\KeywordTok{left_join}\NormalTok{(df2.}\FloatTok{1.}\NormalTok{kemu, }\DataTypeTok{by =} \StringTok{"科目代码"}\NormalTok{) }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{mutate}\NormalTok{(一级科目代码 =}\StringTok{ }\NormalTok{stringr}\OperatorTok{::}\KeywordTok{str_sub}\NormalTok{(科目代码, }\DecValTok{1}\NormalTok{, }\DecValTok{4}\NormalTok{))}
\KeywordTok{names}\NormalTok{(df2.}\DecValTok{1}\NormalTok{)[}\KeywordTok{names}\NormalTok{(df2.}\DecValTok{1}\NormalTok{) }\OperatorTok{==}\StringTok{ "科目名称"}\NormalTok{] <-}\StringTok{ "一级科目"}
\KeywordTok{names}\NormalTok{(df2.}\DecValTok{1}\NormalTok{)[}\KeywordTok{names}\NormalTok{(df2.}\DecValTok{1}\NormalTok{) }\OperatorTok{==}\StringTok{ "明细科目"}\NormalTok{] <-}\StringTok{ "二级科目"}

\NormalTok{hg_replace_na_df_is_num <-}\StringTok{ }\ControlFlowTok{function}\NormalTok{(x) \{ }\KeywordTok{mutate_if}\NormalTok{(x, is.numeric, }\KeywordTok{funs}\NormalTok{(}\KeywordTok{replace}\NormalTok{(., }\KeywordTok{is.na}\NormalTok{(.), }\DecValTok{0}\NormalTok{))) \}}

\NormalTok{df2.}\DecValTok{1}\NormalTok{ <-}\StringTok{ }\KeywordTok{hg_replace_na_df_is_num}\NormalTok{(df2.}\DecValTok{1}\NormalTok{)}

\NormalTok{df2.}\DecValTok{1} \OperatorTok{%>%}\StringTok{ }\KeywordTok{filter}\NormalTok{(一级科目代码 }\OperatorTok{==}\StringTok{ }\NormalTok{kemudaima) }\OperatorTok{%>%}\StringTok{ }
\StringTok{  }\KeywordTok{select}\NormalTok{(序号, 所属月份, 科目代码, 一级科目, 二级科目, }\KeywordTok{everything}\NormalTok{(), }\OperatorTok{-}\NormalTok{一级科目代码) }\OperatorTok{%>%}
\StringTok{  }\CommentTok{#mutate(借方金额 = format(借方金额, nsmall = 2),}
\StringTok{  }\CommentTok{#           贷方金额 = format(贷方金额, nsmall = 2)) %>%}
\StringTok{  }\KeywordTok{sapply}\NormalTok{(as.character) }\OperatorTok{%>%}
\StringTok{  }\NormalTok{knitr}\OperatorTok{::}\KeywordTok{kable}\NormalTok{(}\DataTypeTok{format =}\StringTok{"latex"}\NormalTok{, }\DataTypeTok{booktabs=}\NormalTok{T, }\DataTypeTok{align =} \KeywordTok{c}\NormalTok{(}\StringTok{"ccrccrr"}\NormalTok{))}
\end{Highlighting}
\end{Shaded}

\begin{tabular}{ccrccrrccrccrrccrccrrccrccrrccrccrrccrccrrccrccrr}
\toprule
序号 & 所属月份 & 科目代码 & 一级科目 & 二级科目 & 借方金额 & 贷方金额\\
\midrule
3 & 12 & 113104 & 应收账款 & 供货商4 & 0 & 133292\\
22 & 12 & 113101 & 应收账款 & 供货商1 & 0 & 50000\\
23 & 12 & 113101 & 应收账款 & 供货商1 & 0 & 50000\\
59 & 12 & 113101 & 应收账款 & 供货商1 & 0 & 50000\\
112 & 12 & 113104 & 应收账款 & 供货商4 & 0 & 50000\\
\addlinespace
123 & 12 & 113101 & 应收账款 & 供货商1 & 0 & 50000\\
126 & 12 & 113101 & 应收账款 & 供货商1 & 0 & 50000\\
146 & 12 & 113110 & 应收账款 & 供货商10 & 55160 & 0\\
148 & 12 & 113104 & 应收账款 & 供货商4 & 384000 & 0\\
160 & 12 & 113104 & 应收账款 & 供货商4 & 0 & 60000\\
\addlinespace
162 & 12 & 113101 & 应收账款 & 供货商1 & 0 & 1e+05\\
174 & 12 & 113101 & 应收账款 & 供货商1 & 70720 & 0\\
175 & 12 & 113104 & 应收账款 & 供货商4 & 197400 & 0\\
176 & 12 & 113104 & 应收账款 & 供货商4 & 192000 & 0\\
177 & 12 & 113104 & 应收账款 & 供货商4 & 87024 & 0\\
\addlinespace
178 & 12 & 113104 & 应收账款 & 供货商4 & 288000 & 0\\
179 & 12 & 113104 & 应收账款 & 供货商4 & 76839 & 0\\
181 & 12 & 113109 & 应收账款 & 供货商9 & 20540 & 0\\
182 & 12 & 113101 & 应收账款 & 供货商1 & 29253 & 0\\
183 & 12 & 113108 & 应收账款 & 供货商8 & 58000 & 0\\
\addlinespace
184 & 12 & 113104 & 应收账款 & 供货商4 & 12000 & 0\\
194 & 12 & 113101 & 应收账款 & 供货商1 & 296400 & 0\\
\bottomrule
\end{tabular}

\normalsize

\subsubsection{科目汇总查询}

二级科目汇总查询

\small

\begin{Shaded}
\begin{Highlighting}[]
\NormalTok{df2.}\DecValTok{2}\NormalTok{ <-}\StringTok{ }\NormalTok{df2.}\DecValTok{1} \OperatorTok{%>%}\StringTok{ }\KeywordTok{filter}\NormalTok{(一级科目代码 }\OperatorTok{==}\StringTok{ }\NormalTok{kemudaima) }\OperatorTok{%>%}\StringTok{ }
\StringTok{  }\KeywordTok{group_by}\NormalTok{(一级科目, 二级科目) }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{summarise}\NormalTok{(借方金额 =}\StringTok{ }\KeywordTok{sum}\NormalTok{(借方金额, }\DataTypeTok{na.rm =} \OtherTok{TRUE}\NormalTok{), }
\NormalTok{                贷方金额 =}\StringTok{ }\KeywordTok{sum}\NormalTok{(贷方金额, }\DataTypeTok{na.rm =} \OtherTok{TRUE}\NormalTok{)) }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{arrange}\NormalTok{(}\OperatorTok{-}\NormalTok{借方金额) }\OperatorTok{%>%}
\StringTok{  }\KeywordTok{ungroup}\NormalTok{()}

\NormalTok{df2.}\DecValTok{2}\NormalTok{ <-}\StringTok{ }\KeywordTok{hg_replace_na_df_is_num}\NormalTok{(df2.}\DecValTok{2}\NormalTok{)}

\NormalTok{hg_add_sum_row <-}\StringTok{ }\ControlFlowTok{function}\NormalTok{(df, label.col, comput.cols, }\DataTypeTok{label =} \StringTok{"合计"}\NormalTok{) \{}
\NormalTok{  row.to.add <-}\StringTok{ }\KeywordTok{colSums}\NormalTok{(df[,comput.cols])}
\NormalTok{  row.to.add <-}\StringTok{ }\KeywordTok{as.data.frame}\NormalTok{(}\KeywordTok{t}\NormalTok{(row.to.add))}
\NormalTok{  row.to.add[}\KeywordTok{names}\NormalTok{(df[,label.col])] <-}\StringTok{ }\NormalTok{label}
  \KeywordTok{bind_rows}\NormalTok{(df, row.to.add)}
\NormalTok{\}}
\NormalTok{tmp.name <-}\StringTok{ }\NormalTok{df2.}\DecValTok{2}\NormalTok{[}\DecValTok{1}\NormalTok{,}\DecValTok{1}\NormalTok{][[}\DecValTok{1}\NormalTok{]]}
\NormalTok{df2.}\DecValTok{2}\NormalTok{ <-}\StringTok{ }\NormalTok{df2.}\DecValTok{2} \OperatorTok{%>%}\StringTok{ }\KeywordTok{select}\NormalTok{(}\OperatorTok{-}\DecValTok{1}\NormalTok{) }\OperatorTok{%>%}\StringTok{ }
\StringTok{  }\KeywordTok{hg_add_sum_row}\NormalTok{(}\DecValTok{1}\NormalTok{, }\DecValTok{2}\OperatorTok{:}\DecValTok{3}\NormalTok{)}
\KeywordTok{names}\NormalTok{(df2.}\DecValTok{2}\NormalTok{)[}\KeywordTok{colnames}\NormalTok{(df2.}\DecValTok{2}\NormalTok{) }\OperatorTok{==}\StringTok{ "二级科目"}\NormalTok{] <-}\StringTok{ }\NormalTok{tmp.name}
\NormalTok{df2.}\DecValTok{2} \OperatorTok{%>%}\StringTok{ }
\StringTok{  }\NormalTok{knitr}\OperatorTok{::}\KeywordTok{kable}\NormalTok{(}\DataTypeTok{format =}\StringTok{"latex"}\NormalTok{, }\DataTypeTok{booktabs=}\NormalTok{T, }\DataTypeTok{digits =} \DecValTok{2}\NormalTok{)}
\end{Highlighting}
\end{Shaded}

\begin{tabular}{lrr}
\toprule
应收账款 & 借方金额 & 贷方金额\\
\midrule
供货商4 & 1237263 & 243292\\
供货商1 & 396373 & 350000\\
供货商8 & 58000 & 0\\
供货商10 & 55160 & 0\\
供货商9 & 20540 & 0\\
合计 & 1767336 & 593292\\
\bottomrule
\end{tabular}

\normalsize

\subsubsection{资金习性法}

线性回归预测

\small

\begin{Shaded}
\begin{Highlighting}[]
\NormalTok{df2.}\DecValTok{3}\NormalTok{ <-}\StringTok{ }\NormalTok{readxl}\OperatorTok{::}\KeywordTok{read_excel}\NormalTok{(df.fpath, }\DataTypeTok{sheet =} \StringTok{"c2.3"}\NormalTok{) }
\NormalTok{lm.reg <-}\StringTok{ }\KeywordTok{lm}\NormalTok{(}\DataTypeTok{formula =}\NormalTok{ 资金占用}\OperatorTok{~}\NormalTok{产销量, }\DataTypeTok{data =}\NormalTok{ df2.}\DecValTok{3}\NormalTok{)}
\KeywordTok{summary}\NormalTok{(lm.reg)}
\end{Highlighting}
\end{Shaded}

\begin{verbatim}
## 
## Call:
## lm(formula = 资金占用 ~ 产销量, data = df2.3)
## 
## Residuals:
##      1      2      3      4      5 
##  13.04 -43.91  28.31  35.94 -33.38 
## 
## Coefficients:
##              Estimate Std. Error t value Pr(>|t|)    
## (Intercept) 932.75240   93.64695    9.96 0.002153 ** 
## 产销量        0.38474    0.02305   16.69 0.000468 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 42.05 on 3 degrees of freedom
## Multiple R-squared:  0.9893, Adjusted R-squared:  0.9858 
## F-statistic: 278.6 on 1 and 3 DF,  p-value: 0.0004682
\end{verbatim}

\begin{Shaded}
\begin{Highlighting}[]
\KeywordTok{ggplot}\NormalTok{(df2.}\DecValTok{3}\NormalTok{, }\KeywordTok{aes}\NormalTok{(产销量, 资金占用)) }\OperatorTok{+}
\StringTok{  }\KeywordTok{geom_point}\NormalTok{() }\OperatorTok{+}
\StringTok{  }\KeywordTok{geom_smooth}\NormalTok{(}\DataTypeTok{method =} \StringTok{"lm"}\NormalTok{)}
\end{Highlighting}
\end{Shaded}

\includegraphics{tp391.13-629-1-excel-home-finacial_files/figure-latex/unnamed-chunk-8-1.pdf}

\begin{Shaded}
\begin{Highlighting}[]
\NormalTok{my.pre <-}\StringTok{ }\KeywordTok{data.frame}\NormalTok{(产销量 =}\StringTok{ }\KeywordTok{c}\NormalTok{(}\DecValTok{5000}\NormalTok{, }\DecValTok{5300}\NormalTok{, }\DecValTok{6000}\NormalTok{, }\DecValTok{6400}\NormalTok{))}
\NormalTok{my.predict <-}\StringTok{ }\KeywordTok{predict}\NormalTok{(lm.reg, }\DataTypeTok{newdata =}\NormalTok{  my.pre, }\DataTypeTok{interval =} \StringTok{"prediction"}\NormalTok{, }\DataTypeTok{level =} \FloatTok{0.95}\NormalTok{ )}
\NormalTok{my.pre}\OperatorTok{$}\NormalTok{测算资金占用 <-}\StringTok{ }\NormalTok{my.predict[,}\DecValTok{1}\NormalTok{]}

\NormalTok{hg_kable_latex <-}\StringTok{ }\ControlFlowTok{function}\NormalTok{(df) \{ knitr}\OperatorTok{::}\KeywordTok{kable}\NormalTok{(df, }\DataTypeTok{format =}\StringTok{"latex"}\NormalTok{, }\DataTypeTok{booktabs=}\NormalTok{T, }\DataTypeTok{digits =} \DecValTok{2}\NormalTok{) \}}
\NormalTok{my.pre }\OperatorTok{%>%}\StringTok{ }\KeywordTok{hg_kable_latex}\NormalTok{()}
\end{Highlighting}
\end{Shaded}

\begin{tabular}{rr}
\toprule
产销量 & 测算资金占用\\
\midrule
5000 & 2856.43\\
5300 & 2971.85\\
6000 & 3241.17\\
6400 & 3395.06\\
\bottomrule
\end{tabular}

\normalsize

\end{document}
